﻿using System;
using System.Collections.Generic;
using System.Text;
using Csla;
using System.Data.Odbc;
using MinaspetroLibrary.Configuracoes;
using System.Data;
using Csla.Data;

namespace MinaspetroLibrary.FornecedorPack
{
    [Serializable()]
    public class FornecerdorSetorLigacaoNameValueList : NameValueListBase<Guid, string>
    {
        #region Factory Methods
        private FornecerdorSetorLigacaoNameValueList()
        { /* require use of factory method */ }

        public static FornecerdorSetorLigacaoNameValueList GetFornecerdorSetorLigacaoNameValueList()
        {
            return DataPortal.Fetch<FornecerdorSetorLigacaoNameValueList>(new FilterCriteria());
        }

        public static FornecerdorSetorLigacaoNameValueList GetFornecerdorSetorLigacaoNameValueList(string inicial)
        {
            return DataPortal.Fetch<FornecerdorSetorLigacaoNameValueList>(new FilterCriteria(inicial));
        }
        #endregion //Factory Methods

        #region Data Access
        class FilterCriteria
        {


            public string idFornecedor = string.Empty;

            public FilterCriteria()
            {
            }

            public FilterCriteria(string idFornecedor)
            {
                this.idFornecedor = idFornecedor;
            }
        }

        private void DataPortal_Fetch(FilterCriteria criteria)
        {
            RaiseListChangedEvents = false;

            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            using (OdbcCommand cm = cn.CreateCommand())
            {
                IsReadOnly = false;
                cn.Open();

                cm.CommandType = CommandType.Text;

                StringBuilder strQuery = new StringBuilder();
                strQuery.Append(" SELECT lig.IdSetor, lig.IdFornecedor, sto.Setor ");
                strQuery.Append(" FROM fornecedorsetorligacaotb lig INNER JOIN fornecedorsetortb sto ON sto.Id = lig.IdSetor ");


                if (criteria.idFornecedor != String.Empty)
                {
                    strQuery.Append(" WHERE lig.IdFornecedor = \"" + criteria.idFornecedor.ToString() + "\" ");
                }
                strQuery.Append(" ORDER BY sto.Setor ");


                cm.CommandText = strQuery.ToString();

                using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                {
                    while (dr.Read())
                        this.Add(new NameValuePair(new Guid(dr["IdSetor"].ToString()), dr.GetString("Setor")));
                }
                IsReadOnly = true;
            }//using

            RaiseListChangedEvents = true;
        }

        


        #endregion //Data Access
    }
}
